package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:36
 * @ Description: CarpartstableDaoImpl
 **/

import com.hy.bean.Carpartstable;
import com.hy.dao.CarpartstableDao;
import com.hy.dto.CarDto;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import com.hy.vo.CarpartstableVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class CarpartstableDaoImpl implements CarpartstableDao {
    private QueryRunner qr = new QueryRunner();
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    private static BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
    @Override
    public Carpartstable queryById(Integer id) {
        return null;
    }

    @Override
    public List<Carpartstable> queryAllByLimit(Integer limit, Integer page) {
        Connection conn = null;
        try {
            conn = DBManager.getConnection();

            String sql = "select * from carpartstable limit ?,?";

            List<Carpartstable> carpartstablesList = qr.query(conn,sql,new BeanListHandler<>(Carpartstable.class,basicRowProcessor),(page-1)*limit,limit);

            return carpartstablesList;
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }

    @Override
    public long count() {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select count(1) from carpartstable";
            Long query = qr.query(connection, sql, new ScalarHandler<>());

            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }

    @Override
    public int insert(Connection connection, Carpartstable carpartstable) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String sql = "INSERT INTO `automobile_sales_module`.`carpartstable` (`partCode`, `partName`, `partCreater`, `partCreateDate`, `partPlaceCode`, `producerTable_id`, `partUnit`, `partCubage`, `partWeight`, `partMinStore`, `salePrice`, `costPrice`, `details`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            BigInteger integer =  qr.insert(connection, sql,new ScalarHandler<>(),
                    carpartstable.getPartcode(),
                    carpartstable.getPartname(),
                    carpartstable.getPartcreater(),
                    sdf.format(carpartstable.getPartcreatedate()),
                    carpartstable.getPartplacecode(),
                    carpartstable.getProducertableId(),
                    carpartstable.getPartunit(),
                    carpartstable.getPartcubage(),
                    carpartstable.getPartweight(),
                    carpartstable.getPartminstore(),
                    carpartstable.getSaleprice(),
                    carpartstable.getCostprice(),
                    carpartstable.getDetails());
            return integer.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Carpartstable carpartstable) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        return 0;
    }

    @Override
    public List<Carpartstable> select(String code, String beginDate, String endDate, String creator) {
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
        } catch (Exception e) {
            throw new BizException("建立连接出错啦", e);
        }
        StringBuilder sb = new StringBuilder("select * from carpartstable ");
        List<Object> paramList = new ArrayList<>();
        boolean isWhere = true;

        if (!StringUtils.isBlank(code)) {
            if (isWhere) {
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or partCode like ? ");
            }

            sb.append("partCode like ? ");
            paramList.add("%" + code + "%");
        }

        if (!StringUtils.isBlank(beginDate) && !StringUtils.isBlank(endDate)) {
            if (isWhere) {
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }
            sb.append(" (partCreateDate >= ? and partCreateDate<= ?)");

            paramList.add(beginDate);
            paramList.add(endDate);

        } else if (!StringUtils.isBlank(beginDate) && StringUtils.isBlank(endDate)) {
            if (isWhere) {
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }

            sb.append(" (partCreateDate >= ? )");
            paramList.add(beginDate);
        } else if (!StringUtils.isBlank(beginDate) && StringUtils.isBlank(endDate)) {
            if (isWhere) {
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }

            sb.append(" (partCreateDate<= ?) ");
            paramList.add(endDate);
        }

        if (!StringUtils.isBlank(creator)) {
            if (isWhere) {
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }
            sb.append(" partCreater like ? ");
            paramList.add("%" + creator + "%");
        }

        String sql = sb.toString();
        System.out.println("sql ==================>" + sql);

        List<Carpartstable> carpartstableList = null;
        try {
            if (paramList.size() >= 1)
                carpartstableList = qr.query(conn, sql, new BeanListHandler<Carpartstable>(Carpartstable.class), paramList.toArray());
            else
                carpartstableList = qr.query(conn, sql, new BeanListHandler<Carpartstable>(Carpartstable.class));
        } catch (Exception e) {
            throw new BizException("查询出错啦", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }


        return carpartstableList;
    }

    @Override
    public List<CarpartstableVo> selectAll(Integer currPage, Integer pageSize, CarDto carDto) {
        Connection connection = null;
        String sql = "select car.id id,\n" +
                "car.partName partName,\n" +
                "car.partUnit partUnit,\n" +
                "car.partStore partStore,\n" +
                "car.partCreateDate partcreatedate,\n"+
                "pr.producerName producerName,\n" +
                "car.costPrice costPrice,\n" +
                "round(car.partStore*car.costPrice,3) zje,\n" +
                "car.partMinStore partMinStore\n" +
                " from carpartstable car join producertable pr on car.producerTable_id = pr.id where 1=1";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql(carDto, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return qr.query(connection, sql2, new BeanListHandler<>(CarpartstableVo.class,basicRowProcessor), (currPage - 1) * pageSize, pageSize);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    private Map<String, Object> createSql(CarDto carDto, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StringUtils.isBlank(carDto.getPartcreater())) {
            map.put("partcreater", carDto.getPartcreater());
        } if (carDto.getPartcreatedate() != null) {
            map.put("partcreatedate", carDto.getPartcreatedate());
        } if (carDto.getPartunit() != null) {
            map.put("partunit", carDto.getPartunit());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = carDto.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("partCreateDate")) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                }
                sql += " and " + s + "= '" + s2 + "'";
            }
        }
        sql += " limit ?,?";
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public Long county(CarDto carDto) {
        Connection connection = null;
        String sql = "select count(*) from carpartstable car join producertable pr on car.producerTable_id = pr.id";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql2(carDto, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return qr.query(connection, sql2, new ScalarHandler<>(1));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    private Map<String, Object> createSql2(CarDto carDto, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StringUtils.isBlank(carDto.getPartcreater())) {
            map.put("partcreater", carDto.getPartcreater());
        } if (carDto.getPartcreatedate() != null) {
            map.put("partcreatedate", carDto.getPartcreatedate());
        } if (carDto.getPartunit() != null) {
            map.put("partunit", carDto.getPartunit());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = carDto.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                s2 = map.get(declaredField.getName()).toString();
                if (s.equals("partCreateDate")) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = new Date(s2);
                    s2 = sdf.format(date);
                }
                sql += " and " + s + "= '" + s2 + "'";
            }
        }
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public List<Carpartstable> select() {
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from carpartstable";
            return qr.query(conn, sql, new BeanListHandler<Carpartstable>(Carpartstable.class));
        } catch (Exception e) {
            throw new BizException("Dao出错啦", e);
        } finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }

    }

    //修改库存
    @Override
    public int updatePartStore(Connection connection, Integer id,Integer num) {
        String sql = "update carpartstable set partStore = partStore + ? where id = ?";
        try {
            return qr.update(connection, sql, num, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int updatePartStoreOut(Connection connection, Integer id,Integer num) {
        String sql = "update carpartstable set partStore = ? where id = ?";
        try {
            return qr.update(connection, sql, num, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

//    public static void main(String[] args) throws Exception {
//        CarpartstableDaoImpl cdi = new CarpartstableDaoImpl();
//
//        System.out.println(cdi.select("","","",""));
//        System.out.println(cdi.select("100","","",""));
//        System.out.println(cdi.select("","2001-1-1","2005-1-1","qwe"));
//        System.out.println(cdi.select("wfpeoj","","","efwr"));
//    }
}
